package com.actionsky.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.actionsky.entity.NewsActive;

public class NewsActiveDAO {
	private static Log _log = LogFactory.getLog(NewsActiveDAO.class);
	
	/*根据新闻类型查询新闻动态*/
	public static List<NewsActive> findNewsActiveByType(int type,int page_start,int page_end){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
        ResultSet rs = null;
        List<NewsActive> list = new ArrayList<NewsActive>();
        String sql = "select id,type,news_title,news_abstract,news_content,news_time from news_active where type=? order by news_time desc limit ?,?";
        try{
        	pst = con.prepareStatement(sql);
        	pst.setInt(1, type);
        	pst.setInt(2, page_start);
        	pst.setInt(3, page_end);
        	rs = pst.executeQuery();
        	while(rs.next()){
        		NewsActive newsActive = new NewsActive();
        		newsActive.setId(rs.getInt("id"));
        		newsActive.setType(rs.getInt("type"));
        		newsActive.setNews_title(rs.getString("news_title"));
        		newsActive.setNews_abstract(rs.getString("news_abstract"));
        		newsActive.setNews_content(rs.getString("news_content"));
        		newsActive.setNews_time(rs.getDate("news_time"));
        		list.add(newsActive);
        	}
        	return list;
        }catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return null;
	}
	
	/*根据新闻编号查询新闻内容*/
	public static NewsActive findNewsActiveById(int id){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
        ResultSet rs = null;
        NewsActive newsActive = new NewsActive();
        String sql = "select id,type,news_title,news_abstract,news_content,news_time from news_active where id=?";
        try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			if(rs.next()){
				newsActive.setId(rs.getInt("id"));
				newsActive.setType(rs.getInt("type"));
				newsActive.setNews_title(rs.getString("news_title"));
        		newsActive.setNews_abstract(rs.getString("news_abstract"));
        		newsActive.setNews_content(rs.getString("news_content"));
        		newsActive.setNews_time(rs.getDate("news_time"));
			}
        }catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
		return newsActive;
	}
	
	/*根据新闻类型字查询数据总条数*/
	public static int countByType(int type){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
        ResultSet rs = null;
        int count = 0;
        String sql = "select count(*) from news_active where type=?";
        try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, type);
			rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return count;
	}
	
	/** 订阅最新count条新闻 **/
	public static List<NewsActive> rssNewsFeed(int count){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<NewsActive> list = new ArrayList<NewsActive>();
		String sql = "select id,type,news_title,news_abstract,news_content,news_time from news_active order by news_time desc limit ?";
		try{
        	pst = con.prepareStatement(sql);
        	pst.setInt(1, count);
        	rs = pst.executeQuery();
        	while(rs.next()){
        		NewsActive newsActive = new NewsActive();
        		newsActive.setId(rs.getInt("id"));
        		newsActive.setType(rs.getInt("type"));
        		newsActive.setNews_title(rs.getString("news_title"));
        		newsActive.setNews_abstract(rs.getString("news_abstract"));
        		newsActive.setNews_content(rs.getString("news_content"));
        		newsActive.setNews_time(rs.getTimestamp("news_time"));
        		list.add(newsActive);
        	}
        	return list;
        }catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return null;
	}
	
}
